#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql
import pandas as pd
import xlrd
import xlwt
import os
from xlutils.copy import copy

"""
源文件名字固定为1.xls
源文件sheet页固定为Sheet1

结果文件名固定为匹配结果.xls
结果文件sheet页固定为“匹配结果”

未匹配上人员名单无硬性要求，代码中写死Sheet页名为Sheet1
"""

source_file = r'C:\Users\Administrator\Desktop\1.xls'
result_file = r'C:\Users\Administrator\Desktop\匹配结果.xls'

def match(i,list,result):

    if not os.path.exists(result_file):
        wb = xlwt.Workbook()
        ws = wb.add_sheet('匹配结果')
    else :
        rb = xlrd.open_workbook(result_file, formatting_info=True)
        wb = copy(rb)
        ws = wb.get_sheet('匹配结果')

    font = xlwt.Font()
    font.name = '仿宋_GB2312'
    font.height = 280  # 字体大小，220就是11号字体，大概就是11*20得来的吧

    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern().SOLID_PATTERN
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']

    #ali = xlwt.Alignment()

    style = xlwt.XFStyle()
    style.font = font

    y_style = xlwt.XFStyle()
    y_style.font = font
    y_style.pattern = pattern

    ws.col(0).width = 2000
    ws.col(1).width = 1000
    ws.col(2).width = 20000
    ws.col(3).width = 2000

    if len(result) == 1 :
        res = result[0]
        """
        print(i)
        print(res)
        print(list)
        """
        if res[0] == list[0] and res[1] == list[1] and res[2] == list[2] and res[3] == list[3]:
            ws.row(i).set_style(xlwt.easyxf('font:height 300;'))
            ws.write(i, 0, list[0])
            ws.write(i, 1, list[1])
            ws.write(i, 2, list[2])
            ws.write(i, 3, list[3])
            ws.write(i, 4, 'OK')
        else:
            ws.row(i).set_style(xlwt.easyxf('font:height 300;'))
            ws.write(i, 0, list[0], y_style)
            ws.write(i, 1, list[1], y_style)
            ws.write(i, 2, list[2], y_style)
            ws.write(i, 3, list[3], y_style)
            ws.write(i, 4, res[0], y_style)
            ws.write(i, 5, res[1], y_style)
            ws.write(i, 6, res[2], y_style)
            ws.write(i, 7, res[3], y_style)
    else :
        ws.row(i).set_style(xlwt.easyxf('font:height 600;'))  # 行高37.5
        ws.write(i, 0, list[0], y_style)
        ws.write(i, 1, list[1], y_style)
        ws.write(i, 2, list[2], y_style)
        ws.write(i, 3, list[3], y_style)
        ws.write(i, 4, "不止一个或者没找到", y_style)

    wb.save(result_file)

def match1():
    wb = xlrd.open_workbook(source_file)
    ws = wb.sheet_by_name("Sheet1")
    rows = ws.nrows
    for r in range(rows):
        list = ws.row_values(r)
        name = list[0]
        conn = pymysql.connect("localhost", "root", "password", "contacts", charset='utf8')
        sql = "select 姓名,性别,单位及职务,身份 from 省总委员表 where 姓名 = '%s'" % name
        cursor = conn.cursor()
        cursor.execute(sql)
        res = cursor.fetchall()
        cursor.close()
        conn.close()
        match(r, list, res)

def remain():
    conn = pymysql.connect("localhost", "root", "password", "contacts", charset='utf8')
    sql = "select * from 省总委员表 order by 所属ID ASC, 内部顺序 ASC"
    frame = pd.read_sql(sql, conn)

    wb = xlrd.open_workbook(source_file)
    ws = wb.sheet_by_name("Sheet1")
    rows = ws.nrows

    for r in range(rows):
        list = ws.row_values(r)
        name = list[0]
        frame = frame.drop(frame[frame.姓名 == name ].index)
    frame.to_excel(r'C:\Users\Administrator\Desktop\未匹配上人员名单.xls')

match1()
remain()